<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 4.2.1">
  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">


<link rel="stylesheet" href="/lib/font-awesome/css/all.min.css">

<script id="hexo-configurations">
    var NexT = window.NexT || {};
    var CONFIG = {"hostname":"www.lixiaofei.cn","root":"/","scheme":"Mist","version":"7.8.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":false,"show_result":false,"style":null},"back2top":{"enable":true,"sidebar":false,"scrollpercent":false},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":false,"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":true,"storage":true,"lazyload":true,"nav":{"valine":{"order":-1}}},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":false,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}}};
  </script>

  <meta name="description" content="数据是现代大小厂的重要资产，保护和恢复数据成为了重要的技能， 最近几年，常有一些无良程序员删库跑路的情况，不仅给所在企业更是给自己造成重大的损失， 另外，即使不是故意的情况下，也会因为疏忽造成数据误操作，是一件及麻烦又头疼的事情……">
<meta property="og:type" content="article">
<meta property="og:title" content="妈妈再也不用担心我把数据弄丢了">
<meta property="og:url" content="http://www.lixiaofei.cn/2021-06-04-binlog2sql/index.html">
<meta property="og:site_name" content="管窥程序">
<meta property="og:description" content="数据是现代大小厂的重要资产，保护和恢复数据成为了重要的技能， 最近几年，常有一些无良程序员删库跑路的情况，不仅给所在企业更是给自己造成重大的损失， 另外，即使不是故意的情况下，也会因为疏忽造成数据误操作，是一件及麻烦又头疼的事情……">
<meta property="og:locale" content="zh_CN">
<meta property="article:published_time" content="2021-06-17T22:17:48.000Z">
<meta property="article:modified_time" content="2021-06-17T22:17:48.271Z">
<meta property="article:author" content="太阳雪">
<meta property="article:tag" content="编程">
<meta property="article:tag" content="MySql">
<meta property="article:tag" content="数据库">
<meta name="twitter:card" content="summary">

<link rel="canonical" href="http://www.lixiaofei.cn/2021-06-04-binlog2sql/">


<script id="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome : false,
    isPost : true,
    lang   : 'zh-CN'
  };
</script>

  <title>妈妈再也不用担心我把数据弄丢了 | 管窥程序</title>
  






  <noscript>
  <style>
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-header { opacity: initial; }

  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript>

</head>

<body itemscope itemtype="http://schema.org/WebPage">
  <div class="container use-motion">
    <div class="headband"></div>

    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏">
      <span class="toggle-line toggle-line-first"></span>
      <span class="toggle-line toggle-line-middle"></span>
      <span class="toggle-line toggle-line-last"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <h1 class="site-title">管窥程序</h1>
      <span class="logo-line-after"><i></i></span>
    </a>
      <p class="site-subtitle" itemprop="description">Today is a gift</p>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
    </div>
  </div>
</div>




<nav class="site-nav">
  <ul id="menu" class="main-menu menu">
        <li class="menu-item menu-item-home">

    <a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a>

  </li>
        <li class="menu-item menu-item-about">

    <a href="/about/" rel="section"><i class="fa fa-user fa-fw"></i>关于</a>

  </li>
        <li class="menu-item menu-item-tags">

    <a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签</a>

  </li>
        <li class="menu-item menu-item-categories">

    <a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类</a>

  </li>
        <li class="menu-item menu-item-archives">

    <a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档</a>

  </li>
  </ul>
</nav>




</div>
    </header>

    
  <div class="back-to-top">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>
  <div class="reading-progress-bar"></div>

  <a href="https://github.com/alisx" class="github-corner" title="Follow me on GitHub" aria-label="Follow me on GitHub" rel="noopener" target="_blank"><svg width="80" height="80" viewBox="0 0 250 250" aria-hidden="true"><path d="M0,0 L115,115 L130,115 L142,142 L250,250 L250,0 Z"></path><path d="M128.3,109.0 C113.8,99.7 119.0,89.6 119.0,89.6 C122.0,82.7 120.5,78.6 120.5,78.6 C119.2,72.0 123.4,76.3 123.4,76.3 C127.3,80.9 125.5,87.3 125.5,87.3 C122.9,97.6 130.6,101.9 134.4,103.2" fill="currentColor" style="transform-origin: 130px 106px;" class="octo-arm"></path><path d="M115.0,115.0 C114.9,115.1 118.7,116.5 119.8,115.4 L133.7,101.6 C136.9,99.2 139.9,98.4 142.2,98.6 C133.8,88.0 127.5,74.4 143.8,58.0 C148.5,53.4 154.0,51.2 159.7,51.0 C160.3,49.4 163.2,43.6 171.4,40.1 C171.4,40.1 176.1,42.5 178.8,56.2 C183.1,58.6 187.2,61.8 190.9,65.4 C194.5,69.0 197.7,73.2 200.1,77.6 C213.8,80.2 216.3,84.9 216.3,84.9 C212.7,93.1 206.9,96.0 205.4,96.6 C205.1,102.4 203.0,107.8 198.3,112.5 C181.9,128.9 168.3,122.5 157.7,114.1 C157.9,116.9 156.7,120.9 152.7,124.9 L141.0,136.5 C139.8,137.7 141.6,141.9 141.8,141.8 Z" fill="currentColor" class="octo-body"></path></svg></a>


    <main class="main">
      <div class="main-inner">
        <div class="content-wrap">
          

          <div class="content post posts-expand">
            

    
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="http://www.lixiaofei.cn/2021-06-04-binlog2sql/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.png">
      <meta itemprop="name" content="太阳雪">
      <meta itemprop="description" content="总结 技术 思考 想法">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="管窥程序">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          妈妈再也不用担心我把数据弄丢了
        </h1>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">发表于</span>

              <time title="创建时间：2021-06-18 06:17:48" itemprop="dateCreated datePublished" datetime="2021-06-18T06:17:48+08:00">2021-06-18</time>
            </span>
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-folder"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/python/" itemprop="url" rel="index"><span itemprop="name">python</span></a>
                </span>
            </span>

          
            <span id="/2021-06-04-binlog2sql/" class="post-meta-item leancloud_visitors" data-flag-title="妈妈再也不用担心我把数据弄丢了" title="阅读次数">
              <span class="post-meta-item-icon">
                <i class="fa fa-eye"></i>
              </span>
              <span class="post-meta-item-text">阅读次数：</span>
              <span class="leancloud-visitors-count"></span>
            </span>
  
  <span class="post-meta-item">
    
      <span class="post-meta-item-icon">
        <i class="far fa-comment"></i>
      </span>
      <span class="post-meta-item-text">Valine：</span>
    
    <a title="valine" href="/2021-06-04-binlog2sql/#valine-comments" itemprop="discussionUrl">
      <span class="post-comments-count valine-comment-count" data-xid="/2021-06-04-binlog2sql/" itemprop="commentCount"></span>
    </a>
  </span>
  
  

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
        <p>数据是现代大小厂的重要资产，保护和恢复数据成为了重要的技能，</p>
<p>最近几年，常有一些无良程序员删库跑路的情况，不仅给所在企业更是给自己造成重大的损失，</p>
<p>另外，即使不是故意的情况下，也会因为疏忽造成数据误操作，是一件及麻烦又头疼的事情……</p>
<a id="more"></a>
<h2 id="神器出场">神器出场</h2>
<p>最近的一个项目里，客户数据因为维护不当，导致数据丢失，为了挽回数据，并建立一个跨网闸（内部组网之间不通，无法使用 MySql 主从同步）的数据备份机制，发现了一个神器 <a href="https://github.com/danfengcao/binlog2sql" target="_blank" rel="noopener" title="binlog2sql">binlog2sql</a>。</p>
<p>研究了一番之后，不仅恢复了误操作丢失的数据，还通过 <code>binlog2sql</code> 将主服务器上的 <a href="https://laijianfeng.org/2019/03/MySQL-Binlog-%E4%BB%8B%E7%BB%8D/" target="_blank" rel="noopener" title="binlog">binlog</a> 转化为 SQL 语句，存入文件，实现了数据同步！</p>
<h3 id="安装">安装</h3>
<p><code>binlog2sql</code> 使用 Python 开发，所以需要 Python 环境，可参考 <a href="https://mp.weixin.qq.com/s/Blori_V2ZYmiXUn7rg4mFQ" target="_blank" rel="noopener">Python 环境搭建</a></p>
<p>将 binlog2sql 用 git 克隆的本地，GitHub 上的地址是: <a href="https://github.com/danfengcao/binlog2sql.git" target="_blank" rel="noopener">https://github.com/danfengcao/binlog2sql.git</a></p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">git <span class="built_in">clone</span> https://github.com/danfengcao/binlog2sql.git</span><br></pre></td></tr></table></figure>
<p>通过 binlog2sql 目标下的 <code>requirements.txt</code> 安装依赖包</p>
<blockquote>
<p>提示：推荐在 Python 虚拟环境中安装，创建虚拟环境可参考 <a href="https://mp.weixin.qq.com/s/NJLjflbn3ru9iftVFJQC1g" target="_blank" rel="noopener">Python 虚拟环境 看这一篇就够了</a></p>
</blockquote>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">pip install -r requirements.txt</span><br></pre></td></tr></table></figure>
<p>一切顺利的话，很快就可完成安装。</p>
<p>命令行进入 binlog2sql 代码目录下测试一下</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">&gt; python binlog2sql.py</span><br><span class="line"></span><br><span class="line">usage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD ...]] [-P PORT] [--start-file START_FILE] [--start-position START_POS] [--stop-file END_FILE] [--stop-position END_POS]</span><br><span class="line">                     [--start-datetime START_TIME] [--stop-datetime STOP_TIME] [--save-as SAVE_AS] [--stop-never] [--<span class="built_in">help</span>] [-d [DATABASES ...]] [-t [TABLES ...]] [--only-dml]</span><br><span class="line">                     [--sql-type [SQL_TYPE ...]] [-K] [-B] [--back-interval BACK_INTERVAL]</span><br><span class="line"></span><br><span class="line">Parse MySQL binlog to SQL you want</span><br><span class="line"></span><br><span class="line">...&lt;省略&gt;...</span><br></pre></td></tr></table></figure>
<p>由于没加任何参数，所以打印出使用说明，那说明安装正常了。</p>
<h3 id="简介">简介</h3>
<p><code>binlog2sql</code> 是通过分析 MySql 数据库的 binlog 文件，从中解析出需要执行的 sql 语句的。</p>
<p>那么使用时需要提供一些必要的参数，其中重要的有数据库服务器链接信息，需要分析的 binlog 文件名等，</p>
<p>还可以指定解析的起始和结束位置，以及开始和结束时间。</p>
<h2 id="身手不凡">身手不凡</h2>
<p>是骡子是马拉出来溜溜。</p>
<h3 id="恢复被删数据">恢复被删数据</h3>
<p>假如库表 <code>tb_user</code> 中的数据如下：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">+----+--------+---------------------+</span><br><span class="line">| id | name   | createtime          |</span><br><span class="line">+----+--------+---------------------+</span><br><span class="line">|  1 | 张三   | 2021-01-10 00:04:33 |</span><br><span class="line">|  2 | 李四   | 2021-01-10 00:04:48 |</span><br><span class="line">|  3 | 王五   | 2021-04-23 20:25:00 |</span><br><span class="line">|  4 | 赵六   | 2021-06-04 11:21:23 |</span><br><span class="line">+----+--------+---------------------+</span><br></pre></td></tr></table></figure>
<p>这时不小心执行了一个删操作，将数据误删了</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">delete</span> <span class="keyword">from</span> tb_user</span><br></pre></td></tr></table></figure>
<p>如何恢复呢？</p>
<p>我们看一下数据库的日志情况</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> <span class="keyword">master</span> <span class="keyword">status</span>;</span><br></pre></td></tr></table></figure>
<p>会看到类似这样的结果</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">+------------------+-----------+</span><br><span class="line">| File             | Position  |</span><br><span class="line">+------------------+-----------+</span><br><span class="line">| mysql-bin.000002 |     13136 |</span><br><span class="line">+------------------+-----------+</span><br></pre></td></tr></table></figure>
<blockquote>
<p>注意：只有 MySql 数据库打开了日志记录功能，才能查询到，打开日志功能请参考 <a href="https://juejin.cn/post/6854573218485944333" target="_blank" rel="noopener" title="binlog日志开启和使用">binlog日志开启和使用</a></p>
</blockquote>
<p>可以看出，目前日志记录在文件 mysql-bin.000002 中，当前最新的记录位置是 12546 行</p>
<p>假如当时误操作的时间是上午 11点半左右（可能着急吃饭，没注意），那么预估一个时间范围，比如 11点25 到 11点35，看看一下当时的操作：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">python binlog2sql -h127.0.0.1 -P3306 -uadmin -p<span class="string">'admin'</span> -dtest -t tb_user --start-file=<span class="string">'mysql-bin.000002'</span> --start-datetime=<span class="string">'2021-06-04 11:25:00'</span> --stop-datetime=<span class="string">'2021-06-04 11:35:00'</span></span><br></pre></td></tr></table></figure>
<p>输出为：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`test`</span>.<span class="string">`tb_user`</span>(<span class="string">`createtime`</span>, <span class="string">`id`</span>, <span class="string">`name`</span>) <span class="keyword">VALUES</span> (<span class="string">'2021-06-04 11:21:23'</span>, <span class="number">4</span>, <span class="string">'李四'</span>); <span class="comment">#start 12317 end 12487 time 2021-06-04 11:21:23</span></span><br><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> <span class="string">`test`</span>.<span class="string">`tb_user`</span> <span class="keyword">WHERE</span> <span class="string">`createtime`</span>=<span class="string">'2021-01-10 00:04:33'</span> <span class="keyword">AND</span> <span class="string">`id`</span>=<span class="number">1</span> <span class="keyword">AND</span> <span class="string">`name`</span>=<span class="string">'张三'</span> <span class="keyword">LIMIT</span> <span class="number">1</span>; <span class="comment">#start 12728 end 12829 time 2021-06-04 11:27:32</span></span><br><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> <span class="string">`test`</span>.<span class="string">`tb_user`</span> <span class="keyword">WHERE</span> <span class="string">`createtime`</span>=<span class="string">'2021-01-10 00:04:48'</span> <span class="keyword">AND</span> <span class="string">`id`</span>=<span class="number">2</span> <span class="keyword">AND</span> <span class="string">`name`</span>=<span class="string">'李四'</span> <span class="keyword">LIMIT</span> <span class="number">1</span>; <span class="comment">#start 12728 end 12829 time 2021-06-04 11:27:32</span></span><br><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> <span class="string">`test`</span>.<span class="string">`tb_user`</span> <span class="keyword">WHERE</span> <span class="string">`createtime`</span>=<span class="string">'2021-04-23 20:25:00'</span> <span class="keyword">AND</span> <span class="string">`id`</span>=<span class="number">3</span> <span class="keyword">AND</span> <span class="string">`name`</span>=<span class="string">'王五'</span> <span class="keyword">LIMIT</span> <span class="number">1</span>; <span class="comment">#start 12728 end 12829 time 2021-06-04 11:27:32</span></span><br><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> <span class="string">`test`</span>.<span class="string">`tb_user`</span> <span class="keyword">WHERE</span> <span class="string">`createtime`</span>=<span class="string">'2021-06-04 11:21:23'</span> <span class="keyword">AND</span> <span class="string">`id`</span>=<span class="number">4</span> <span class="keyword">AND</span> <span class="string">`name`</span>=<span class="string">'赵六'</span> <span class="keyword">LIMIT</span> <span class="number">1</span>; <span class="comment">#start 12728 end 12829 time 2021-06-04 11:27:32</span></span><br></pre></td></tr></table></figure>
<p>可以看出，第二行开始到第五行为删除语句，查看语句最后的起始和结束位置 <code>start 12728 end 12829</code></p>
<p>即 binlog 中，删除执行的位置在 12728-12829 之间，于是锁定精确位置，生成回滚语句：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">python binlog2sql -h127<span class="number">.0</span><span class="number">.0</span><span class="number">.1</span> -P3306 -uadmin -p<span class="string">'admin'</span> -dtest -t tb_user --start-file=<span class="string">'mysql-bin.000002'</span> --start-position=<span class="number">12728</span> --stop-position=<span class="number">12829</span> -B</span><br></pre></td></tr></table></figure>
<blockquote>
<p>注意参数 <strong>-B</strong>，意思是生成回滚 SQL，即生成的是撤销之前操作的语句</p>
</blockquote>
<p>输出为：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`test`</span>.<span class="string">`tb_user`</span>(<span class="string">`createtime`</span>, <span class="string">`id`</span>, <span class="string">`name`</span>) <span class="keyword">VALUES</span> (<span class="string">'2021-06-04 11:21:23'</span>, <span class="number">4</span>, <span class="string">'赵六'</span>); <span class="comment">#start 12728 end 12829 time 2016-12-13 20:28:05</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`test`</span>.<span class="string">`tb_user`</span>(<span class="string">`createtime`</span>, <span class="string">`id`</span>, <span class="string">`name`</span>) <span class="keyword">VALUES</span> (<span class="string">'2021-04-23 20:25:00'</span>, <span class="number">3</span>, <span class="string">'王五'</span>); <span class="comment">#start 12728 end 12829 time 2016-12-13 20:28:05</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`test`</span>.<span class="string">`tb_user`</span>(<span class="string">`createtime`</span>, <span class="string">`id`</span>, <span class="string">`name`</span>) <span class="keyword">VALUES</span> (<span class="string">'2021-01-10 00:04:48'</span>, <span class="number">2</span>, <span class="string">'李四'</span>); <span class="comment">#start 12728 end 12829 time 2016-12-13 20:28:05</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`test`</span>.<span class="string">`tb_user`</span>(<span class="string">`createtime`</span>, <span class="string">`id`</span>, <span class="string">`name`</span>) <span class="keyword">VALUES</span> (<span class="string">'2021-01-10 00:04:33'</span>, <span class="number">1</span>, <span class="string">'张三'</span>); <span class="comment">#start 12728 end 12829 time 2016-12-13 20:28:05</span></span><br></pre></td></tr></table></figure>
<p>从输出的语句来看，顺序是删除的倒序，而且已经将原来的 delete 语句改为了 insert 语句，也就是原来操作的逆操作</p>
<p>如果确认语句没问题，执行生成的语句就可以了</p>
<p>是不是既方便又高效呢？</p>
<h3 id="解析-SQL">解析 SQL</h3>
<p><code>binlog2sql</code> 功能强大，使用起来也很方便，看看其他功能吧。</p>
<p>作为一个命令行工具，功能都体现在参数里，可分为 解析模式、解析目标、解析范围三部分。</p>
<h4 id="解析模式">解析模式</h4>
<p><code>binlog2sql</code> 支持两个解析模式，默认的是单次解析，即运行一次解析一次，</p>
<p>还可以支持持续解析，即不间断地从目标数据库地 binlog 中解析出 sql 来，持续解析通过参数 <code>--never-stop</code> 开启，</p>
<p>开启之后，线程不会退出，一直处于运行状态，会自动判断 binlog 的变化，对变化部分增量式解析。</p>
<p>这种模式可以用于数据库同步，不过生产上使用前，最好考虑各种异常情况，比如重启，网络中断等情况。</p>
<p>参数 <code>-K</code> 或 <code>--no-primany-key</code> 表示的去除 <code>INSERT</code> 语句中的主键，这个在数据汇总的场景下很方便，可以避免多个数据源中主键冲突的问题。</p>
<p>参数 <code>-B</code> 或 <code>--flashback</code>，表示回滚模式，在上面的例子中展示过，即会解析成逆操作的 sql 语句。</p>
<p>在回滚模式下，每生成一千条 SQL 语句会加一个 SLEEP 语句，是为以免数据执行时产生拥堵，默认为 1 秒，可以通过 <code>--back-interval</code> 参数来设置，</p>
<p>例如 <code>--back-interval 2</code> 表示暂停 2 秒。</p>
<h4 id="解析目标">解析目标</h4>
<p>MySql 设置 binlog 时可以指定记录哪个库，以及哪些表，即目标。</p>
<p>那么用 <code>binlog2sql</code> 也可以指定解析目标。</p>
<p>参数 <code>-d</code> 或 <code>--databases</code> 用于指定数据库，如果多个库，用空格分隔，例如 <code>-d db1 db2</code>；</p>
<p>参数 <code>-t</code> 或 <code>--tables</code> 用于指定库表，多个库表用空格分隔，例如 <code>-t tb1 tb2</code>。</p>
<p>如果指定解析目标不仅效率更高，而且分析和执行解析的结果也更方便。</p>
<h4 id="解析范围">解析范围</h4>
<p>范围包括 binlog <code>文件范围</code>，<code>时间范围</code> 以及 <code>行范围</code>，例如前面例子中用到了 <code>时间范围</code> 和 <code>行范围</code>。</p>
<p><code>文件范围</code> 用 <code>--start-file</code> 和 <code>--stop-file</code> 参数来指定，只需要提供 binlog 文件名即可，不需要写全路径，这是因为，<code>binlog2sql</code> 会自动根据目标服务器配置读取 binlog 文件；</p>
<p><code>时间范围</code> 用  <code>--start-datetime</code> 和 <code>--stop-datetime</code> 参数来指定，时间格式为 <code>%Y-%m-%d %H:%M:%S</code>；</p>
<p><code>行范围</code> 用 <code>--start-position</code> 和 <code>--stop-position</code> 参数来指定，也可以简写为 <code>--start-pos</code> 和 <code>--end-pos</code>。</p>
<h2 id="深入了解">深入了解</h2>
<p><code>binlog2sql</code> 不仅是一个实用的工具，而且也是个研究和学习的好例子。</p>
<p>只有不到 500 行代码，很容易阅读；</p>
<p>阅读源码，不仅能深入了解其实现原理，而且还可以学习到很多好用法。</p>
<h3 id="实现原理">实现原理</h3>
<p><code>binlog2sql</code> 的原理是，利用 <code>pymysql</code> 从目标服务器上获取 binlog 信息，然后锁定范围，使用 <code>pymysqlreplication</code> 解析 binlog 文件，最后，得到需要解析出的 sql 语句。</p>
<p>在这基础上，做了一些功能性扩展，比如解析范围，解析模式等，相对来说比较简单，很容易看懂。</p>
<h3 id="命令行参数">命令行参数</h3>
<p>编程时处理命令行参数是机械而繁琐的，特别是有不同性质的性质和别名的参数</p>
<p><code>binlog2sql</code> 中利用了 <a href="https://docs.python.org/zh-cn/3/library/argparse.html" target="_blank" rel="noopener" title="argparse 模块">argparse 模块</a>，</p>
<p><code>argparse</code> 模块可以让人轻松编写用户友好的命令行接口。程序定义它需要的参数，<code>argparse</code> 可以从 <code>sys.argv</code> 解析出提供的命令行参数。而且 argparse 模块还会自动生成帮助和使用手册，并在用户给程序传入无效参数时报出错误信息。</p>
<p>很容易就能编程高大上的命令行程序接口，再也不用为很 low 的程序接口发愁了。</p>
<h3 id="文件处理上下文（context）">文件处理上下文（context）</h3>
<p><code>binlog2sql</code> 在回滚模式（即提供了参数 <code>-B</code>）中，使用了一个临时文件记录解析出来的 SQL 语句，并且在完成之后删除。</p>
<p>一般来说，在完成后主动删除文件即可，不过如果能利用 <code>with</code> 块的资源回收功能就更好了。</p>
<p>查看源码，会看到一个创建文件写法：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@contextmanager</span></span><br><span class="line"><span class="function"><span class="keyword">def</span> <span class="title">temp_open</span><span class="params">(filename, mode)</span>:</span></span><br><span class="line">    f = open(filename, mode)</span><br><span class="line">    <span class="keyword">try</span>:</span><br><span class="line">        <span class="keyword">yield</span> f</span><br><span class="line">    <span class="keyword">finally</span>:</span><br><span class="line">        f.close()</span><br><span class="line">        os.remove(filename)</span><br></pre></td></tr></table></figure>
<p><a href="https://www.liaoxuefeng.com/wiki/1016959663602400/1115615597164000" target="_blank" rel="noopener" title="@contextmanager">@contextmanager</a> 指示器可以将一个<a href="https://www.programiz.com/python-programming/generator" target="_blank" rel="noopener" title="生成器">生成器</a>，作为一个<a href="https://www.geeksforgeeks.org/context-manager-in-python/" target="_blank" rel="noopener" title="上下文管理器">上下文管理器</a>，</p>
<p>那么：</p>
<p><code>with</code> 声明部分，会执行前会执行 <code>yield</code> 语句之前的部分</p>
<p><code>with</code> 范围内，会执行 <code>yield</code> 语句，即返回一个需要后续处理的对象，比如文件，后续处理是关闭</p>
<p><code>with</code> 执行完成前，会执行 <code>yield</code> 语句之后的代码</p>
<p>那么这段代码的意义就是，当文件使用完成后，关闭文件，并且删除掉。</p>
<p>使用方式为：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">with</span> temp_open(tmp_file, <span class="string">"w"</span>) <span class="keyword">as</span> f_tmp</span><br><span class="line">    ...</span><br><span class="line">    f_tmp.write(sql + <span class="string">'\n'</span>)</span><br><span class="line">    ...</span><br></pre></td></tr></table></figure>
<p>这样无论如何只要 <code>with</code> 块执行完，文件就会被删除，不用担心忘记，是不是很优雅？</p>
<p>除了这两点，还有很多值得把玩的地方，有兴趣的话可以读读源码。</p>
<h2 id="总结">总结</h2>
<p>无论是什么工具，都需要有一定的基础和良好的习惯上才会发挥作用，比如得开启 MySql 的 binlog 日志，并由记录工作的习惯。</p>
<p>同时，任何工具方法都有它的特点，可以在了解功能的同时，研究一下其使用原理，是一个很好的技能提升机会。</p>
<p>很多人在抱怨，没有应用场景，没有实际项目，其实研究这些工具，就会有事半功倍的效果。</p>
<p>比心</p>

    </div>

    
    
    

      <footer class="post-footer">
          <div class="post-tags">
              <a href="/tags/%E7%BC%96%E7%A8%8B/" rel="tag"># 编程</a>
              <a href="/tags/MySql/" rel="tag"># MySql</a>
              <a href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/" rel="tag"># 数据库</a>
          </div>

        

			<div id="wechat_subscriber" style="display: block; padding: 10px 0; margin: 20px auto; width: 100%; text-align: center">
    <img id="wechat_subscriber_qcode" src="/images/wechat-qcode.png" alt="太阳雪 wechat" style="width: 200px; max-width: 100%;"/>
    <div>关注我，获得更多精彩内容</div>
</div>

        
		
    <div class="post-nav">
      <div class="post-nav-item">
    <a href="/2021-05-27-a-simple-game/" rel="prev" title="与其说教，不如一起写个游戏">
      <i class="fa fa-chevron-left"></i> 与其说教，不如一起写个游戏
    </a></div>
      <div class="post-nav-item">
    <a href="/2021-06-18-experience-class/" rel="next" title="定投人生课堂 体验课">
      定投人生课堂 体验课 <i class="fa fa-chevron-right"></i>
    </a></div>
    </div>
      </footer>
    
  </article>
  
  
  



          </div>
          
    <div class="comments" id="valine-comments"></div>

<script>
  window.addEventListener('tabs:register', () => {
    let { activeClass } = CONFIG.comments;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      let commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }
</script>

        </div>
          
  
  <div class="toggle sidebar-toggle">
    <span class="toggle-line toggle-line-first"></span>
    <span class="toggle-line toggle-line-middle"></span>
    <span class="toggle-line toggle-line-last"></span>
  </div>

  <aside class="sidebar">
    <div class="sidebar-inner">

      <ul class="sidebar-nav motion-element">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <!--noindex-->
      <div class="post-toc-wrap sidebar-panel">
          <div class="post-toc motion-element"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#神器出场"><span class="nav-number">1.</span> <span class="nav-text">神器出场</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#安装"><span class="nav-number">1.1.</span> <span class="nav-text">安装</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#简介"><span class="nav-number">1.2.</span> <span class="nav-text">简介</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#身手不凡"><span class="nav-number">2.</span> <span class="nav-text">身手不凡</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#恢复被删数据"><span class="nav-number">2.1.</span> <span class="nav-text">恢复被删数据</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#解析-SQL"><span class="nav-number">2.2.</span> <span class="nav-text">解析 SQL</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#解析模式"><span class="nav-number">2.2.1.</span> <span class="nav-text">解析模式</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#解析目标"><span class="nav-number">2.2.2.</span> <span class="nav-text">解析目标</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#解析范围"><span class="nav-number">2.2.3.</span> <span class="nav-text">解析范围</span></a></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#深入了解"><span class="nav-number">3.</span> <span class="nav-text">深入了解</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#实现原理"><span class="nav-number">3.1.</span> <span class="nav-text">实现原理</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#命令行参数"><span class="nav-number">3.2.</span> <span class="nav-text">命令行参数</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#文件处理上下文（context）"><span class="nav-number">3.3.</span> <span class="nav-text">文件处理上下文（context）</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#总结"><span class="nav-number">4.</span> <span class="nav-text">总结</span></a></li></ol></div>
      </div>
      <!--/noindex-->

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
    <img class="site-author-image" itemprop="image" alt="太阳雪"
      src="/images/avatar.png">
  <p class="site-author-name" itemprop="name">太阳雪</p>
  <div class="site-description" itemprop="description">总结 技术 思考 想法</div>
</div>
<div class="site-state-wrap motion-element">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
          <a href="/archives/">
        
          <span class="site-state-item-count">88</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
            <a href="/categories/">
          
        <span class="site-state-item-count">14</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
            <a href="/tags/">
          
        <span class="site-state-item-count">47</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>



      </div>

    </div>
  </aside>
  <div id="sidebar-dimmer"></div>


      </div>
    </main>

    <footer class="footer">
      <div class="footer-inner">
        

        

<div class="copyright">
  
  &copy; 
  <span itemprop="copyrightYear">2022</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">太阳雪</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" class="theme-link" rel="noopener" target="_blank">Hexo</a> & <a href="https://mist.theme-next.org/" class="theme-link" rel="noopener" target="_blank">NexT.Mist</a> 强力驱动
  </div>

        








      </div>
    </footer>
  </div>

  
  <script src="/lib/anime.min.js"></script>
  <script src="/lib/velocity/velocity.min.js"></script>
  <script src="/lib/velocity/velocity.ui.min.js"></script>

<script src="/js/utils.js"></script>

<script src="/js/motion.js"></script>


<script src="/js/schemes/muse.js"></script>


<script src="/js/next-boot.js"></script>




  
  <script>
    (function(){
      var canonicalURL, curProtocol;
      //Get the <link> tag
      var x=document.getElementsByTagName("link");
		//Find the last canonical URL
		if(x.length > 0){
			for (i=0;i<x.length;i++){
				if(x[i].rel.toLowerCase() == 'canonical' && x[i].href){
					canonicalURL=x[i].href;
				}
			}
		}
    //Get protocol
	    if (!canonicalURL){
	    	curProtocol = window.location.protocol.split(':')[0];
	    }
	    else{
	    	curProtocol = canonicalURL.split(':')[0];
	    }
      //Get current URL if the canonical URL does not exist
	    if (!canonicalURL) canonicalURL = window.location.href;
	    //Assign script content. Replace current URL with the canonical URL
      !function(){var e=/([http|https]:\/\/[a-zA-Z0-9\_\.]+\.baidu\.com)/gi,r=canonicalURL,t=document.referrer;if(!e.test(r)){var n=(String(curProtocol).toLowerCase() === 'https')?"https://sp0.baidu.com/9_Q4simg2RQJ8t7jm9iCKT-xh_/s.gif":"//api.share.baidu.com/s.gif";t?(n+="?r="+encodeURIComponent(document.referrer),r&&(n+="&l="+r)):r&&(n+="?l="+r);var i=new Image;i.src=n}}(window);})();
  </script>















  

  


<script>
NexT.utils.loadComments(document.querySelector('#valine-comments'), () => {
  NexT.utils.getScript('//unpkg.com/valine/dist/Valine.min.js', () => {
    var GUEST = ['nick', 'mail', 'link'];
    var guest = 'nick,mail';
    guest = guest.split(',').filter(item => {
      return GUEST.includes(item);
    });
    new Valine({
      el         : '#valine-comments',
      verify     : false,
      notify     : false,
      appId      : 'teRj7yeJj0xpfGKLJ5uFdbIu-gzGzoHsz',
      appKey     : '3uie0p1nxouCCnkjiXTEBeLd',
      placeholder: "您的关注是我的动力",
      avatar     : 'mm',
      meta       : guest,
      pageSize   : '10' || 10,
      visitor    : true,
      lang       : 'zh-cn' || 'zh-cn',
      path       : location.pathname,
      recordIP   : false,
      serverURLs : ''
    });
  }, window.Valine);
});
</script>

</body>
</html>
